package sadi.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedHashMap;

public class DBHandler
{
	//Instance Variables
	private boolean isConnected;
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost/";
	private String database = "sadisharedwiki";
	private String username = "root";
	private String password = "root"; // password for the MySQL server
	private Connection conn;
	private ResultSet result;
	private PreparedStatement userValidSQL, userLevelSQL, registerSQL, loginSQL;
	private PreparedStatement searchWikiSQL, getHighlightSQL, getStatusSQL;
	private PreparedStatement wikiValidSQL, wikiOwnerSQL, addWikiSQL, viewWikiSQL,viewWiki2SQL, editWikiSQL, deleteWikiSQL, setWikiStatusSQL, setWikiStatus2SQL;
	private PreparedStatement addReportSQL, deleteReportSQL;
	
	public static void main(String [] args)
	{
		DBHandler db = new DBHandler();		
		//db.getApprovalList();
	}
	
	public DBHandler()
	{
		try
		{
			//Make Connection to Database
			Class.forName(driver);			
			conn = DriverManager.getConnection(url + database, username, password);
			
			//Init Prepared SQL Statements
			initStatements();
			
			isConnected = true;
		}
		catch(SQLException e1)
		{
			try
			{
				//No Such Database Detected - Create Database
				conn = DriverManager.getConnection(url, username, password);
				Statement statement = conn.createStatement();
				statement.executeUpdate("CREATE DATABASE " + database);
				statement.executeUpdate("USE " + database);
				
				//Create User Table
				String createUserTable = 	"CREATE TABLE user (" +
												"userID int(11) NOT NULL auto_increment, " +
												"username varchar(25) NOT NULL, " +
												"password varchar(25) NOT NULL, " +
												"userLevel enum('user', 'admin') NOT NULL, " +
												"PRIMARY KEY (userID), " +
												"UNIQUE KEY(username)" +
											");" ;
				statement.executeUpdate(createUserTable);
				
				//Create Wiki Table
				String createWikiTable = 	"CREATE TABLE wiki (" +
												"wikiID int(11) NOT NULL auto_increment, " +
												"title varchar(50) NOT NULL, " +
												"description varchar(150) NOT NULL, " +
												"content varchar(500), " +
												"createdby int(11) NOT NULL, " +
												"createddatetime datetime NOT NULL, " +
												"modifiedby int(11), " +
												"modifieddatetime datetime, " +
												"status enum('p','a','r') NOT NULL, " +
												"PRIMARY KEY (wikiID)" +
											");";
				statement.executeUpdate(createWikiTable);
				
				//Create Wiki Report Table
				String createReportTable = 	"CREATE TABLE report (" +
												"reportID int(11) NOT NULL auto_increment, " +
												"wikiID int(11) NOT NULL, " +
												"reportedby int(11) NOT NULL, " +
												"reporteddatetime datetime NOT NULL, " +
												"PRIMARY KEY(reportID)" +
											");";
				statement.executeUpdate(createReportTable);
				
				//Insert Dummy Data (For Testing Only)
				insertDummyData(statement);
				
				//Init Prepared SQL Statements
				initStatements();
				
				isConnected = true;
			}
			catch(Exception e2)
			{
				isConnected = false;
				//e2.printStackTrace();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	//Get Database Connection Status
	public boolean isConnected()
	{
		return isConnected;
	}
	
	
	
	/*************** INTERNAL CONVENIENCE METHODS ***************/
	
	//Insert Dummy Data (FOR TESTING ONLY)
	private void insertDummyData(Statement statement)
	{
		try
		{
			//Insert Dummy Users
			String dummyUser = 	"INSERT INTO user (userID, username, password, userLevel) VALUES " +
								"(1, 'test1', 'test1', 'user'), " +
								"(2, 'test2', 'test2', 'user'), " +
								"(3, 'admin1', 'admin1', 'admin');";
			statement.executeUpdate(dummyUser);
			
			//Insert Dummy Wiki
			String dummyWiki = 	"INSERT INTO wiki (wikiID, title, description, content, createdby, createddatetime, status) VALUES " +
								"(1, 'Tchaikovsky', 'The Life of Me', 'blablabla', 1, '2010-06-01 09:30:30', 'a'), "+
								"(2, 'Beethoven', 'The Great Composer', 'blablabla', 2, '2010-06-02 10:01:01', 'a'), " + 
								"(3, 'Title1', 'Desc1', 'Content1', 2, '2010-06-02 10:01:01', 'a'), " + 
								"(4, 'Title2', 'Desc2', 'Content2', 2, '2010-06-02 10:01:01', 'p'), " +
								"(5, 'Title3', 'Desc3', 'Content3', 2, '2010-06-02 10:01:01', 'a') ";
			statement.executeUpdate(dummyWiki);
			
			//Insert Dummy Report
			String dummyReport =	"INSERT INTO report (reportID, wikiID, reportedby, reporteddatetime) VALUES " +
									"(1, 1, 3, '2010-06-03 10:01:01'), " +
									"(2, 1, 2, '2010-06-03 10:01:01');";
			statement.executeUpdate(dummyReport);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	//Initialized Prepared SQL Statements
	private void initStatements()
	{
		try
		{
			//User Handling SQL
			userValidSQL = conn.prepareStatement("SELECT userID FROM user WHERE userID=?;");
			userLevelSQL = conn.prepareStatement("SELECT userLevel FROM user WHERE userID=?;");
			registerSQL = conn.prepareStatement("INSERT INTO user (username, password, userLevel) VALUES (?, ?, ?);");
			loginSQL = conn.prepareStatement("SELECT userID FROM user WHERE username=? AND password=?;");
		
			//Wiki Listing Handling SQL
			searchWikiSQL = conn.prepareStatement("SELECT wikiID, title, description FROM wiki WHERE title LIKE ? AND (status='a' OR status='r');");
			getHighlightSQL = conn.prepareStatement("SELECT wikiID, title, description FROM wiki WHERE (status='a' OR status='r') ORDER BY createddatetime DESC LIMIT 10;");
			getStatusSQL = conn.prepareStatement("SELECT wikiID, title, username FROM wiki JOIN user ON wiki.createdby = user.userID WHERE status=?;");
		
			//Wiki Handling SQL
			wikiValidSQL = conn.prepareStatement("SELECT wikiID FROM wiki WHERE wikiID=?;");
			wikiOwnerSQL = conn.prepareStatement("SELECT wikiID FROM wiki WHERE wikiID=? AND createdby=?;");
			addWikiSQL = conn.prepareStatement("INSERT INTO wiki (title, description, content, createdby, createddatetime, status) VALUES (?, ?, ?, ?, ?, ?);");
			viewWikiSQL = conn.prepareStatement("SELECT wikiID, title, description, content, createdby FROM wiki WHERE wikiID=? AND (status='a' OR status='r');");
			viewWiki2SQL = conn.prepareStatement("SELECT wikiID, title, description, content, createdby FROM wiki WHERE wikiID=?;");
			editWikiSQL = conn.prepareStatement("UPDATE wiki SET title=?, description=?, content=?, modifiedby=?, modifieddatetime=?, status=? WHERE wikiID=?;");
			deleteWikiSQL = conn.prepareStatement("DELETE FROM wiki WHERE wikiID=?;");
			setWikiStatusSQL = conn.prepareStatement("UPDATE wiki SET status=? WHERE wikiID=? AND status!=?;");
			setWikiStatus2SQL = conn.prepareStatement("UPDATE wiki SET status=? WHERE wikiID=?;");
			
			//Report Handling SQL
			addReportSQL = conn.prepareStatement("INSERT INTO report (wikiID, reportedby, reporteddatetime) VALUES (?, ?, ?);");
			deleteReportSQL = conn.prepareStatement("DELETE FROM report WHERE wikiId=?;");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	
	
	/*************** USER HANDLING METHODS ***************/

	//Check Existence of UserID
	public boolean isValidUser(int userID)
	{
		try
		{
			userValidSQL.setInt(1, userID);
			result = userValidSQL.executeQuery();
			
			while(result.next())
				return true;

			//No Such User Found
			return false;
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return false;
		}	
	}
	
	//Check User's Administrative Rights
	public boolean isAdmin(int userID)
	{
		try
		{
			userLevelSQL.setInt(1, userID);
			result = userLevelSQL.executeQuery();
			
			while(result.next())
				return (result.getString(1).equals("admin"));

			//No Such User Found
			return false;
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return false;
		}	
	}
	
	//Register User
	public boolean registerUser(String username, String password)
	{
		try
		{
			registerSQL.setString(1, username);
			registerSQL.setString(2, password);
			registerSQL.setString(3, "user");		
			
			return (registerSQL.executeUpdate() == 1);
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	//Login User
	public int loginUser(String username, String password)
	{
		try
		{
			loginSQL.setString(1, username);
			loginSQL.setString(2, password);			
			result = loginSQL.executeQuery();

			//Successful Login
			while(result.next())
				return result.getInt("userID");

			//Unsuccessful Login
			return -1;
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return -1;
		}	
	}
	
	
	
	/*************** WIKI LISTING HANDLING METHODS ***************/
	
	//Search Wiki Matches
	public ArrayList<ArrayList<String>> searchWiki(String title)
	{
		ArrayList<String> temp;
		ArrayList<ArrayList<String>> response = new ArrayList<ArrayList<String>>();
		
		try
		{
			searchWikiSQL.setString(1, "%" + title + "%");
			result = searchWikiSQL.executeQuery();
			
			while(result.next())
			{
				temp = new ArrayList<String>();
				temp.add(Integer.toString(result.getInt(1)));
				temp.add(result.getString(2));
				temp.add(result.getString(3));
				response.add(temp);
			}
			
			return response;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return response;
		}
	}
	
	//Get Latest 10 Wiki Highlights
	public ArrayList<ArrayList<String>> getHighlightList()
	{
		ArrayList<String> temp;
		ArrayList<ArrayList<String>> response = new ArrayList<ArrayList<String>>();
		
		try
		{
			result = getHighlightSQL.executeQuery();
			
			while(result.next())
			{
				temp = new ArrayList<String>();
				temp.add(Integer.toString(result.getInt(1)));
				temp.add(result.getString(2));
				temp.add(result.getString(3));
				response.add(temp);
			}
			
			return response;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return response;
		}
	}
	
	//Get List of Wiki with the Specified Status
	public ArrayList<ArrayList<String>> getStatusList(String status)
	{
		ArrayList<String> temp;
		ArrayList<ArrayList<String>> response = new ArrayList<ArrayList<String>>();
		
		try
		{
			getStatusSQL.setString(1, status);
			result = getStatusSQL.executeQuery();
			
			while(result.next())
			{
				temp = new ArrayList<String>();
				temp.add(Integer.toString(result.getInt(1)));
				temp.add(result.getString(2));
				temp.add(result.getString(3));
				response.add(temp);
			}
			
			return response;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return response;
		}
	}
	
	
	
	/*************** WIKI HANDLING METHODS ***************/
	
	//Check Existence of WikiID
	public boolean isValidWiki(int wikiID)
	{
		try
		{
			wikiValidSQL.setInt(1, wikiID);
			result = wikiValidSQL.executeQuery();
			
			while(result.next())
				return true;

			//No Such User Found
			return false;
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return false;
		}	
	}
	
	//Check Wiki Owner
	public boolean isWikiOwner(int wikiID, int userID)
	{
		try
		{
			wikiOwnerSQL.setInt(1, wikiID);
			wikiOwnerSQL.setInt(2, userID);
			result = wikiOwnerSQL.executeQuery();
			
			while(result.next())
				return true;

			//No Such Wiki Found With Specified Owner
			return false;
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			return false;
		}	
	}
	
	//Create a New Wiki With Content
	public boolean addWiki(String title, String description, String content, int userID)
	{
		try
		{
			addWikiSQL.setString(1, title);
			addWikiSQL.setString(2, description);
			addWikiSQL.setString(3, content);
			addWikiSQL.setInt(4, userID);
			addWikiSQL.setTimestamp(5, new Timestamp(Calendar.getInstance().getTime().getTime()));
			addWikiSQL.setString(6, "p");
			
			addWikiSQL.executeUpdate();
			return true;	
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	//View Content of Wiki
	public ArrayList<String> viewWiki(int wikiID)
	{
		ArrayList<String> wiki = new ArrayList<String>();
		
		try
		{
			viewWikiSQL.setInt(1, wikiID);
			result = viewWikiSQL.executeQuery();
			
			while(result.next())
			{
				wiki.add(Integer.toString(result.getInt(1)));
				wiki.add(result.getString(2));
				wiki.add(result.getString(3));
				wiki.add(result.getString(4));
				wiki.add(Integer.toString(result.getInt(5)));
			}
			
			return wiki;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return wiki;
		}
	}
	
	//Edit Wiki Contents
	public boolean editWiki(int wikiID, String title, String description, String content, int userID)
	{
		try
		{
			editWikiSQL.setString(1, title);
			editWikiSQL.setString(2, description);
			editWikiSQL.setString(3, content);
			editWikiSQL.setInt(4, userID);
			editWikiSQL.setTimestamp(5, new Timestamp(Calendar.getInstance().getTime().getTime()));
			editWikiSQL.setString(6, "p");
			editWikiSQL.setInt(7, wikiID);			
			
			return (editWikiSQL.executeUpdate() == 1);
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	//Delete Wiki
	public boolean deleteWiki(int wikiID)
	{
		try
		{
			deleteWikiSQL.setInt(1, wikiID);
			
			if(deleteWikiSQL.executeUpdate() > 0)
			{
				deleteReportSQL.setInt(1, wikiID);
				deleteReportSQL.executeUpdate();
				return true;
			}
			else
				return false;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	
	//Approve Wiki Contents
	public boolean approveWiki(int wikiID)
	{
		try
		{
			setWikiStatusSQL.setString(1, "a");
			setWikiStatusSQL.setInt(2, wikiID);		
			setWikiStatusSQL.setString(3, "a");
			return (setWikiStatusSQL.executeUpdate() == 1);			
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	//Report Wiki Contents
	public boolean addReport(int wikiID, int userID)
	{
		try
		{
			setWikiStatus2SQL.setString(1, "r");
			setWikiStatus2SQL.setInt(2, wikiID);	
			
			if(setWikiStatus2SQL.executeUpdate() > 0)
			{
				addReportSQL.setInt(1, wikiID);
				addReportSQL.setInt(2, userID);
				addReportSQL.setTimestamp(3, new Timestamp(Calendar.getInstance().getTime().getTime()));
				addReportSQL.executeUpdate();
				return true;
			}
			else
				return false;
		}
		catch(SQLException e)
		{
			//e.printStackTrace();
			return false;
		}
	}
	
	//View Content of Wiki
	public ArrayList<String> viewAllWiki(int wikiID)
	{
		ArrayList<String> wiki = new ArrayList<String>();
		
		try
		{
			viewWiki2SQL.setInt(1, wikiID);
			result = viewWiki2SQL.executeQuery();
			
			while(result.next())
			{wiki.add(Integer.toString(result.getInt(1)));
			wiki.add(result.getString(2));
			wiki.add(result.getString(3));
			wiki.add(result.getString(4));
			wiki.add(Integer.toString(result.getInt(5)));
			return wiki;
		}
		
		return wiki;
	}
	catch(SQLException e)
	{
		//e.printStackTrace();
		return wiki;
	}
}


}
	
	